package com.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.javabean.BookBean;

public class Book {
	public List listbook(int page){
		int value1=3*(page-1);
		List list = new ArrayList();
		
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from book limit "+value1+",3 ";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			while(rSet.next()){
				BookBean bean = new BookBean();
				bean.setIdbook(rSet.getString("idbook"));
				bean.setBookname(rSet.getString("bookname"));
				bean.setAuthor(rSet.getString("author"));
				bean.setIsbn(rSet.getString("isbn"));
				bean.setPublisher(rSet.getString("publisher"));
				bean.setAddress(rSet.getString("address"));
				bean.setCatalog(rSet.getString("catalog"));
				bean.setCLC(rSet.getString("CLC"));
				bean.setContent(rSet.getString("content"));
				bean.setFormat(rSet.getString("format"));
				bean.setPrices(rSet.getString("prices"));
				bean.setPages(rSet.getString("pages"));
				bean.setStore(rSet.getInt("store"));
				list.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rSet.close();
			statement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public List searchBook(String target,String arg,int page){
		List list = new ArrayList();
		
		int value = 3*(page-1);
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from book where "+target+"='"+arg+"' limit "+value+",3";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			while(rSet.next()){
				BookBean bean = new BookBean();
				bean.setIdbook(rSet.getString("idbook"));
				bean.setBookname(rSet.getString("bookname"));
				bean.setAuthor(rSet.getString("author"));
				bean.setIsbn(rSet.getString("isbn"));
				bean.setPublisher(rSet.getString("publisher"));
				bean.setAddress(rSet.getString("address"));
				bean.setCatalog(rSet.getString("catalog"));
				bean.setCLC(rSet.getString("CLC"));
				bean.setContent(rSet.getString("content"));
				bean.setFormat(rSet.getString("format"));
				bean.setPrices(rSet.getString("prices"));
				bean.setPages(rSet.getString("pages"));
				bean.setStore(rSet.getInt("store"));
				list.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rSet.close();
			statement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public void addBook(BookBean bBean){
		
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		
		String sql = "insert into book (bookname,author,isbn,CLC,publisher,address,content,catalog,prices,pages,format,store)"
				+ "values('"+bBean.getBookname()+"','"+bBean.getAuthor()+"','"+bBean.getIsbn()+"','"+bBean.getCLC()+"','"+bBean.getPublisher()+"'"
						+ ",'"+bBean.getAddress()+"','"+bBean.getContent()+"','"+bBean.getCatalog()+"','"+bBean.getPrices()+"','"+bBean.getPages()+"','"+bBean.getFormat()+"','"+bBean.getStore()+"')";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sManege.dbclose(connection, statement);
	}
	public void updateBook(BookBean bBean){
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		
		String sql = "update book set bookname = '"+bBean.getBookname()+"',author = '"+bBean.getAuthor()+"',isbn = '"+bBean.getIsbn()+"',CLC = '"+bBean.getCLC()+"',publisher = '"+bBean.getPublisher()+""
				+ "',address = '"+bBean.getAddress()+"',content = '"+bBean.getContent()+"',catalog = '"+bBean.getCatalog()+"',prices = '"+bBean.getPrices()+"',pages = '"+bBean.getPages()+"',format = '"+bBean.getFormat()+""
						+ "',store = '"+bBean.getStore()+"' where idbook = '"+bBean.getIdbook()+"'";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sManege.dbclose(connection, statement);
		
	}
	public void delBook(String idbook){

		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "delete from book where idbook='"+idbook+"'";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sManege.dbclose(connection, statement);
	}
	public int getRows(){
		int result = 0;
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from book";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			if(rSet.next()){
			rSet.last();
			result=rSet.getRow();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	public int getRows(String target,String arg){
		int result = 0;
		
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from book where "+target+"='"+arg+"'";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			rSet.last();
			result = rSet.getRow();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
}
